import smtplib
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

from flask import Flask, render_template, request, jsonify

import photo
from flask_cors import CORS
import dbutils

app = Flask(__name__)

CORS(app)


@app.route('/')
def index():
    """系统首页"""
    try:
        conn = dbutils.connect_db()
        with conn.cursor() as cursor:
            cursor.execute('SELECT id, name FROM tb_class where status = 1 order by id desc')
            classes = cursor.fetchall()
    finally:
        dbutils.close_db()

    return render_template('index.html', classes=classes)


@app.route('/get_students/<class_code>')
def get_students(class_code):
    """根据班级ID 查询所有的学生信息"""
    try:
        conn = dbutils.connect_db()
        with conn.cursor() as cursor:
            cursor.execute('SELECT id, name FROM tv_user WHERE class_id = %s and type = 1', args=(int(class_code),))
            students = cursor.fetchall()
    finally:
        dbutils.close_db()

    return jsonify(students)


@app.route('/send_email', methods=['POST'])
def send_email():
    """发送邮件"""
    student_name = request.form.get('name')

    if student_name is None:
        return jsonify({'status': 'fail', 'message': '学生ID参数丢失'}), 400

    # 获取数据库链接
    conn = dbutils.connect_db()
    with conn.cursor() as cursor:
        cursor.execute('SELECT email, name FROM tv_user WHERE id = %s and type = 1', args=(int(student_name),))
        student = cursor.fetchone()

    dbutils.close_db()

    conn = dbutils.connect_db(user="root", passwd="qiku-root", db="db")
    with conn.cursor() as cursor:
        # 查询该学生是否已经有咖啡券
        cursor.execute("select id, uid, NOD, email, username from coffeevdata where email = %s and nod = 0",
                       args=(student['email'],))
        coffees = cursor.fetchall()

    if len(coffees) > 0:
        return jsonify({'status': 'fail', 'message': '该学生有咖啡券暂未使用'}), 400

    # 准备发送邮件
    smtp_server = 'smtp.163.com'
    smtp_port = 465
    smtp_user = '17752558704@163.com'
    smtp_pass = 'EDdJmNqiYjdsG6AK'

    # tp_email = "472759903@qq.com"
    tp_email = student['email']

    msg = MIMEMultipart("related")
    msg['Subject'] = '奇酷内部咖啡券'
    msg['To'] = tp_email
    msg['From'] = smtp_user

    msg_alternative = MIMEMultipart('alternative')
    msg.attach(msg_alternative)

    # 发送邮件
    try:
        text = MIMEText('<h1>咖啡券</h1><img src="cid:image1" />', "html", "utf-8")
        msg_alternative.attach(text)
        buffered = photo.send_photo(student['name'], student['email'], request)
        msg_image = MIMEImage(buffered.read(), _subtype="png")
        msg_image.add_header('Content-ID', '<image1>')
        msg.attach(msg_image)

        with smtplib.SMTP_SSL(smtp_server, smtp_port) as server:  # 使用SMTP_SSL启用SSL加密
            server.login(smtp_user, smtp_pass)
            server.send_message(msg)

        conn.commit()
        return jsonify({'status': 'ok', 'message': '操作成功、邮件已发送'}), 200
    except Exception as e:
        conn.rollback()
        return jsonify({'status': 'fail', 'message': f'邮件发送失败, {e}'}), 400
    finally:
        dbutils.close_db()


@app.route('/QR')
def process_qr():
    user_uid = request.args.get('uid')
    user_email = request.args.get('email')
    user_name = request.args.get('name')
    # 处理信息
    if user_email and user_uid and user_name:
        # 这里可以添加你的业务逻辑，例如存储到数据库、进行验证等
        try:
            conn2 = dbutils.connect_db(user="root", passwd="qiku-root", db="db")
            with conn2.cursor() as cursor:
                cursor.execute("SELECT id, NOD FROM coffeevdata  where uid=%s", args=(user_uid,))
                # 获取咖啡券信息
                coffee = cursor.fetchone()

            if coffee is None:
                return render_template("result.html", **{"msg": "您的二维码信息有误", "status": "error"})

            # 检查 咖啡券是否已经使用
            if coffee['NOD'] != 0:
                return render_template("result.html",
                                       **{"msg": "咖啡券已失效, 请勿重复使用该咖啡券", "status": "error"})

            # 使用咖啡券
            sql = "update coffeevdata set NOD = 1 where uid = %s"
            with conn2.cursor() as cursor:
                cursor.execute(sql, args=(user_uid,))

            conn2.commit()
        finally:
            dbutils.close_db()

        return render_template("result.html",
                               **{"msg": f"编号:NO.{coffee['id']:0>10} 的咖啡券使用成功", "status": "ok"})

    return render_template("result.html", **{"msg": "缺失必要的参数", "status": "error"})


if __name__ == '__main__':
    app.static_folder = 'static'
    app.run(debug=True, port=5000, host='0.0.0.0')
